﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.Enums;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;

namespace NS
{
    [HotUpdate]
    [Description("供应商应付账期")]
    public class AccountsPayablePeriodReport : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "序号";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("供应商应付账期", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "序号";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "序号");

            DateTime FStartDate;
            DateTime FEndDate;
            //string FMaterialid = "";
            string year = "";

            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            //StringBuilder SQLFilter1 = new StringBuilder();
            FStartDate = Convert.ToDateTime(dyFilter["F_WNEG_Date"]);//开始日期
            //FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//结束
            //if(!FStartDate.ToString().Contains("0001"))
            //{
            //    SQLFilter.Append(" and t.FDATE<=dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,'"+ FStartDate + "')+1, 0)) and t.FDATE>=DATEADD(yy, DATEDIFF(yy,0,'"+ FStartDate + "'), 0)");
            //}
            if (!FStartDate.ToString().Contains("0001"))
            {
                year = FStartDate.ToString("yyyy");
            }
            ////过滤 日期
            //if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            //{
            //    SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            //}
            ////if (!dyFilter.IsNullOrEmpty())
            ////{
            //    if (!Convert.ToString(dyFilter["FMaterialid_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t1.FMATERIALID='" + dyFilter["FMaterialid_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            //    }
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
----------------------------------------固定样式（客户要求）-------------------------------------------------------------------------
CREATE table  #AA(XH INT,FORGID INT,FCOUNTRY varchar(20),FORG varchar(20))
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(1,100119,'泰国','MCPP')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(2,100120,'泰国','天美')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(3,100121,'泰国','泰旺')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(4,100122,'泰国','泰宠')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(5,100123,'泰国','panda')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(6,184111,'泰国','海洋')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(7,184112,'泰国','八爪鱼')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(8,378013,'泰国','海鹰')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(9,402770,'泰国','皮皮虾')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(10,100125,'泰国','海狸')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(11,100126,'菲律宾','K2K')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(12,100127,'菲律宾','海豹')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(13,100128,'菲律宾','海螺')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(14,100130,'印尼','考拉')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(15,184108,'印尼','海星')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(16,100124,'印尼','海鲸')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(17,184109,'印尼','海马')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(18,100131,'马来','海鲨')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(19,184110,'马来','海象')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(20,235316,'马来','海蜂')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(21,100129,'越南','海豚')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(22,385925,'台湾','海狮')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(23,100132,'墨西哥','海雕')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(24,102413,'','综合部')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(25,410716,'','YCK')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(26,449335,'菲律宾','海蛇')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(27,458135,'中国台湾','海苔')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(28,466197,'新加坡','海兔')
insert into #AA(XH,FORGID,FCOUNTRY,FORG)VALUES(29,524309,'印尼','海熊')
----------------------------------------------1月--------------------------------------------------------------
select * into #A from (
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-01-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-01-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
union all
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT   from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT =''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-01-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-01-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)t

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公1月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账1月
INTO #B
FROM #A

SELECT 项目,sum(对公1月)对公1月,sum(个账1月)个账1月
INTO #C
FROM #B
group by 项目





----------------------------------------------2月--------------------------------------------------------------
SELECT * INTO #A2 FROM (
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-02-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-02-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-02-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-02-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公2月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账2月
INTO #B2
FROM #A2

SELECT 项目,sum(对公2月)对公2月,sum(个账2月)个账2月
INTO #C2
FROM #B2
group by 项目

----------------------------------------------3月--------------------------------------------------------------
SELECT * INTO #A3  FROM (
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-03-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-03-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT   from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-03-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-03-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公3月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账3月
INTO #B3
FROM #A3

SELECT 项目,sum(对公3月)对公3月,sum(个账3月)个账3月
INTO #C3
FROM #B3
group by 项目

----------------------------------------------4月--------------------------------------------------------------
SELECT * INTO #A4 FROM (
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-04-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-04-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-04-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-04-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公4月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账4月
INTO #B4
FROM #A4

SELECT 项目,sum(对公4月)对公4月,sum(个账4月)个账4月
INTO #C4
FROM #B4
group by 项目


----------------------------------------------5月--------------------------------------------------------------
SELECT * INTO #A5 FROM (
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-05-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-05-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT='' 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-05-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-05-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公5月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账5月
INTO #B5
FROM #A5
SELECT 项目,sum(对公5月)对公5月,sum(个账5月)个账5月
INTO #C5
FROM #B5
group by 项目

----------------------------------------------6月--------------------------------------------------------------
SELECT * INTO #A6 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-06-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-06-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT  from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT='' 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-06-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-06-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公6月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账6月
INTO #B6
FROM #A6
SELECT 项目,sum(对公6月)对公6月,sum(个账6月)个账6月
INTO #C6
FROM #B6
group by 项目

----------------------------------------------7月--------------------------------------------------------------
SELECT * INTO #A7 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-07-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-07-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-07-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-07-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公7月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账7月
INTO #B7
FROM #A7
SELECT 项目,sum(对公7月)对公7月,sum(个账7月)个账7月
INTO #C7
FROM #B7
group by 项目

----------------------------------------------8月--------------------------------------------------------------
SELECT * INTO #A8 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-08-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-08-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-08-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-08-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公8月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账8月
INTO #B8
FROM #A8
SELECT 项目,sum(对公8月)对公8月,sum(个账8月)个账8月
INTO #C8
FROM #B8
group by 项目

----------------------------------------------9月--------------------------------------------------------------
SELECT * INTO #A9 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-09-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-09-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-09-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-09-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公9月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账9月
INTO #B9
FROM #A9
SELECT 项目,sum(对公9月)对公9月,sum(个账9月)个账9月
INTO #C9
FROM #B9
group by 项目

----------------------------------------------10月--------------------------------------------------------------
SELECT * INTO #A10 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-10-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-10-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-10-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-10-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公10月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账10月
INTO #B10
FROM #A10
SELECT 项目,sum(对公10月)对公10月,sum(个账10月)个账10月
INTO #C10
FROM #B10
group by 项目

----------------------------------------------11月--------------------------------------------------------------
SELECT * INTO #A11 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-11-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-11-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-11-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-11-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T


SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公11月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账11月
INTO #B11
FROM #A11
SELECT 项目,sum(对公11月)对公11月,sum(个账11月)个账11月
INTO #C11
FROM #B11
group by 项目

----------------------------------------------12月--------------------------------------------------------------
SELECT * INTO #A12 FROM(
select t9.fname 项目,T7.FDATAVALUE 银行账号,t5.FALLAMOUNT 实付金额,T7.FENTRYID from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
left join T_BAS_ASSISTANTDATAENTRY_l t7 on t1.F_WNEG_ASSISTANT=t7.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T8 ON T7.FENTRYID=T8.FENTRYID
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' AND T7.FDATAVALUE IS NOT NULL 
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-12-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-12-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
UNION ALL
select t9.fname 项目,'' 银行账号,t5.FALLAMOUNT 实付金额,t1.F_WNEG_ASSISTANT from t_PUR_POOrder t
left join t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join t_BD_Supplier t2 on t.FSupplierId=t2.FSupplierId
--left join T_BAS_ASSISTANTDATAENTRY_l t3 on t1.F_WNEG_ASSISTANT3=t3.FENTRYID
left join t_BD_Supplier_l t4 on t2.FSupplierId=t4.FSupplierId
left join t_PUR_POOrderEntry_F T5 ON T1.FENTRYID=T5.FENTRYID
--left join T_PUR_POORDERINSTALLMENT t6 on t.fid=t6.fid
LEFT JOIN T_ORG_ORGANIZATIONS_L T9 ON T.FPURCHASEORGID=T9.FORGID
where t2.FDOCUMENTSTATUS='C' AND T.FDOCUMENTSTATUS='C' 
AND t1.F_WNEG_ASSISTANT=''
AND (T.FDATE>=CAST(CONVERT(VARCHAR(7), '{2}-12-01', 120) + '-01' AS DATE) 
AND T.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CONVERT(VARCHAR(7), '{2}-12-01', 120) + '-01' AS DATE))))
and t9.FLOCALEID=2052
)T

SELECT 项目,
CASE WHEN FENTRYID IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 对公12月,
CASE WHEN FENTRYID NOT IN('6461ae2f22c051','663dd091d49fb1','66220a72680218','6461ae4022c053')
THEN 实付金额 ELSE 0 END 个账12月
INTO #B12
FROM #A12
SELECT 项目,sum(对公12月)对公12月,sum(个账12月)个账12月
INTO #C12
FROM #B12
group by 项目

select * into #D from (
SELECT 项目,对公1月,个账1月,
0 对公2月,0 个账2月, 
0 对公3月,0 个账3月, 
0 对公4月,0 个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
对公2月,个账2月, 
0 对公3月,0 个账3月, 
0 对公4月,0 个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C2
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
对公3月,个账3月, 
0 对公4月,0 个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C3
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
对公4月,个账4月,
0 对公5月,0 个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C4
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
对公5月,个账5月,
0 对公6月,0 个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C5
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
对公6月,个账6月,
0 对公7月,0 个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C6
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
对公7月,个账7月,
0 对公8月,0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C7
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
对公8月,个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C8
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
对公9月,个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C9
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
0 对公9月,0 个账9月,
对公10月,个账10月,
0 对公11月,0 个账11月,
0 对公12月,0 个账12月
FROM #C10
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
对公11月,个账11月,
0 对公12月,0 个账12月
FROM #C11
UNION ALL
SELECT 项目,
0 对公1月, 0 个账1月,
0 对公2月, 0 个账2月, 
0 对公3月, 0 个账3月, 
0 对公4月, 0 个账4月,
0 对公5月, 0 个账5月,
0 对公6月, 0 个账6月,
0 对公7月, 0 个账7月,
0 对公8月, 0 个账8月,
0 对公9月,0 个账9月,
0 对公10月,0 个账10月,
0 对公11月,0 个账11月,
对公12月,个账12月
FROM #C12
)t

SELECT T1.XH 序号, T1.FORG 项目,T1.FCOUNTRY,
SUM(对公1月)对公1月,
SUM(个账1月)个账1月,
SUM(对公2月)对公2月,
SUM(个账2月)个账2月,
SUM(对公3月)对公3月,
SUM(个账3月)个账3月,
SUM(对公4月)对公4月,
SUM(个账4月)个账4月,
SUM(对公5月)对公5月,
SUM(个账5月)个账5月,
SUM(对公6月)对公6月,
SUM(个账6月)个账6月,
SUM(对公7月)对公7月,
SUM(个账7月)个账7月,
SUM(对公8月)对公8月,
SUM(个账8月)个账8月,
SUM(对公9月)对公9月,
SUM(个账9月)个账9月,
SUM(对公10月)对公10月,
SUM(个账10月)个账10月,
SUM(对公11月)对公11月,
SUM(个账11月)个账11月,
SUM(对公12月)对公12月,
SUM(个账12月)个账12月,
(SUM(对公1月)+SUM(对公2月)+SUM(对公3月)+SUM(对公4月)+SUM(对公5月)+SUM(对公6月)+SUM(对公7月)+SUM(对公8月)+SUM(对公9月)+SUM(对公10月)+SUM(对公11月)+SUM(对公12月))DGHJ,
(SUM(个账1月)+SUM(个账2月)+SUM(个账3月)+SUM(个账4月)+SUM(个账5月)+SUM(个账6月)+SUM(个账7月)+SUM(个账8月)+SUM(个账9月)+SUM(个账10月)+SUM(个账11月)+SUM(个账12月))SZHJ,
(SUM(对公1月)+SUM(对公2月)+SUM(对公3月)+SUM(对公4月)+SUM(对公5月)+SUM(对公6月)+SUM(对公7月)+SUM(对公8月)+SUM(对公9月)+SUM(对公10月)+SUM(对公11月)+SUM(对公12月)+
SUM(个账1月)+SUM(个账2月)+SUM(个账3月)+SUM(个账4月)+SUM(个账5月)+SUM(个账6月)+SUM(个账7月)+SUM(个账8月)+SUM(个账9月)+SUM(个账10月)+SUM(个账11月)+SUM(个账12月))ZHJ
into #table
FROM #D t
inner join #AA T1 ON T.项目=T1.FORG
GROUP BY T1.XH,T1.FORG,T1.FCOUNTRY
select *,{0} into {1} from #table where 项目 is not null
                                        ", seqFld, tableName, year);
                                        DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName) 
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        //protected override void ExecuteBatch(List listSql) { base.ExecuteBatch(listSql); }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            var XH0 = header.AddChild("序号", new LocaleValue("序号"));
            XH0.ColIndex = 0;
            var XH1 = header.AddChild("FCOUNTRY", new LocaleValue("国家"));
            XH1.ColIndex = 1;
            var XH2 = header.AddChild("项目", new LocaleValue("项目"));
            XH2.ColIndex = 2;
            var XH3 = header.AddChild("对公1月", new LocaleValue("1月&&对公"), SqlStorageType.SqlDecimal);
            XH3.ColIndex = 3;
            var XH4 = header.AddChild("个账1月", new LocaleValue("1月&&个账"), SqlStorageType.SqlDecimal);
            XH4.ColIndex = 4;
            var XH5 = header.AddChild("对公2月", new LocaleValue("2月&&对公"), SqlStorageType.SqlDecimal);
            XH5.ColIndex = 5;
            var XH6 = header.AddChild("个账2月", new LocaleValue("2月&&个账"), SqlStorageType.SqlDecimal);
            XH6.ColIndex = 6;
            var XH7 = header.AddChild("对公3月", new LocaleValue("3月&&对公"), SqlStorageType.SqlDecimal);
            XH7.ColIndex = 7;
            var XH8 = header.AddChild("个账3月", new LocaleValue("3月&&个账"), SqlStorageType.SqlDecimal);
            XH8.ColIndex = 8;
            var XH9 = header.AddChild("对公4月", new LocaleValue("4月&&对公"), SqlStorageType.SqlDecimal);
            XH9.ColIndex = 9;
            var XH10 = header.AddChild("个账4月", new LocaleValue("4月&&个账"), SqlStorageType.SqlDecimal);
            XH10.ColIndex = 10;
            var XH11 = header.AddChild("对公5月", new LocaleValue("5月&&对公"), SqlStorageType.SqlDecimal);
            XH11.ColIndex = 11;
            var XH12 = header.AddChild("个账5月", new LocaleValue("5月&&个账"), SqlStorageType.SqlDecimal);
            XH12.ColIndex = 12;
            var XH13 = header.AddChild("对公6月", new LocaleValue("6月&&对公"), SqlStorageType.SqlDecimal);
            XH13.ColIndex = 13;
            var XH14 = header.AddChild("个账6月", new LocaleValue("6月&&个账"), SqlStorageType.SqlDecimal);
            XH14.ColIndex = 14;
            var XH15 = header.AddChild("对公7月", new LocaleValue("7月&&对公"), SqlStorageType.SqlDecimal);
            XH15.ColIndex = 15;
            var XH16 = header.AddChild("个账7月", new LocaleValue("7月&&个账"), SqlStorageType.SqlDecimal);
            XH16.ColIndex = 16;
            var XH17 = header.AddChild("对公8月", new LocaleValue("8月&&对公"), SqlStorageType.SqlDecimal);
            XH17.ColIndex = 17;
            var XH18 = header.AddChild("个账8月", new LocaleValue("8月&&个账"), SqlStorageType.SqlDecimal);
            XH18.ColIndex = 18;
            var XH19 = header.AddChild("对公9月", new LocaleValue("9月&&对公"), SqlStorageType.SqlDecimal);
            XH19.ColIndex = 19;
            var XH20 = header.AddChild("个账9月", new LocaleValue("9月&&个账"), SqlStorageType.SqlDecimal);
            XH20.ColIndex = 20;
            var XH21 = header.AddChild("对公10月", new LocaleValue("10月&&对公"), SqlStorageType.SqlDecimal);
            XH21.ColIndex = 21;
            var XH22 = header.AddChild("个账10月", new LocaleValue("10月&&个账"), SqlStorageType.SqlDecimal);
            XH22.ColIndex = 22;
            var XH23 = header.AddChild("对公11月", new LocaleValue("11月&&对公"), SqlStorageType.SqlDecimal);
            XH23.ColIndex = 23;
            var XH24 = header.AddChild("个账11月", new LocaleValue("11月&&个账"), SqlStorageType.SqlDecimal);
            XH24.ColIndex = 24;
            var XH25 = header.AddChild("对公12月", new LocaleValue("12月&&对公"), SqlStorageType.SqlDecimal);
            XH25.ColIndex = 25;
            var XH26 = header.AddChild("个账12月", new LocaleValue("12月&&个账"), SqlStorageType.SqlDecimal);
            XH26.ColIndex = 26;
            var XH27 = header.AddChild("DGHJ", new LocaleValue("对公合计"), SqlStorageType.SqlDecimal);
            XH27.ColIndex = 27;
            var XH28 = header.AddChild("SZHJ", new LocaleValue("个账合计"), SqlStorageType.SqlDecimal);
            XH28.ColIndex = 28;
            var XH29 = header.AddChild("ZHJ", new LocaleValue("总合计"), SqlStorageType.SqlDecimal);
            XH29.ColIndex = 29;
            return header;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        public override List<Kingdee.BOS.Core.Report.SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            //设置合计列
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("对公1月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账1月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公2月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账2月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公3月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账3月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公4月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账4月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公5月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账5月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公6月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账6月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公7月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账7月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公8月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账8月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公9月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账9月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公10月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账10月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公11月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账11月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("对公12月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("个账12月", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("DGHJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SZHJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("ZHJ", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
            return result;
        }
        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
